﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class CommonMethodDA : ICommonMethodDA
    {
        public CommonMethodDA()
        {
            
        }

        public bool CheckAuthorizationOwnerByUserID(string userID, string ownerID)
        {
            string sql = "SELECT COUNT(1) FROM SYSADM.USER_OWNER WHERE USER_ID = @USER_ID AND OWNER_ID = @OWNER_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID), new SqlParameter("@OWNER_ID", ownerID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, sql, paras) != null);
        }

        public bool CheckAuthorizationOwnerByUserID(DataBase dataBase, DbTransaction tran, string userID, string ownerID)
        {
            string sql = "SELECT COUNT(1) FROM SYSADM.USER_OWNER WHERE USER_ID = @USER_ID AND OWNER_ID = @OWNER_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID), new SqlParameter("@OWNER_ID", ownerID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras) != null);
        }

        public string GenNextNumStr(string tableName, string whLoginID)
        {
            return GenNextNumberDA.GenNextNumStr(tableName, whLoginID);
        }

        public string GenNextNumStr(DataBase dataBase, DbTransaction tran, string sTableName, string whLoginID)
        {
            return GenNextNumberDA.GenNextNumStr(dataBase, tran, sTableName, whLoginID);
        }

        public List<VisualLocationInfo> GetAllVisualLocationInfo(List<string> sqlWhere, string currentWHName)
        {
            string temp = string.Empty;
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            string strSql = ("SELECT L.WH_ID,L.ZONE_ID,L.LOC,L.STATUS,L.LOC_USAGE,L.LOC_CATEGORY,L.LOC_FLAG, L.LOC_HANDLING,L.LENGTH,L.WIDTH,L.HEIGHT, L.[CUBE],L.CUBIC_CAPACITY,L.WEIGHT_CAPACITY, ISNULL(SUM(S.CUBE*ISL.QTY),0)  AS CUBIC_COUNT, L.SYSTEM_CREATED  FROM @_@LOCATION L LEFT JOIN @_@INV_SKU_LOC ISL ON L.LOC = ISL.LOC LEFT JOIN @_@SKU S ON ISL.SKU_ID = S.SKU_ID AND ISL.OWNER_ID=S.OWNER_ID LEFT JOIN @_@INV_LOT_LOC_LPN LLL ON L.LOC=LLL.LOC " + temp + " GROUP BY L.WH_ID,L.ZONE_ID,L.LOC,L.STATUS,L.LOC_USAGE,L.LOC_CATEGORY,L.LOC_FLAG,L.LOC_HANDLING,L.LENGTH,L.WIDTH,L.HEIGHT,L.[CUBE],L.CUBIC_CAPACITY,L.WEIGHT_CAPACITY,L.SYSTEM_CREATED ORDER BY  L.SYSTEM_CREATED DESC, L.ZONE_ID DESC,L.LOC ASC").Replace("@_@", currentWHName);
            List<VisualLocationInfo> VisualLocationList = new List<VisualLocationInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql))
            {
                while (reader.Read())
                {
                    VisualLocationInfo VisualLocation = new VisualLocationInfo(reader["WH_ID"].ToString(), reader["LOC"].ToString(), reader["LOC_USAGE"].ToString(), reader["LOC_CATEGORY"].ToString(), reader["LOC_FLAG"].ToString(), reader["LOC_HANDLING"].ToString(), reader["STATUS"].ToString(), reader["ZONE_ID"].ToString(), Convert.ToDouble(reader["LENGTH"].ToString()), Convert.ToDouble(reader["WIDTH"].ToString()), Convert.ToDouble(reader["HEIGHT"].ToString()), Convert.ToDouble(reader["CUBE"].ToString()), Convert.ToDouble(reader["CUBIC_CAPACITY"].ToString()), Convert.ToDouble(reader["WEIGHT_CAPACITY"].ToString()), Convert.ToDouble(reader["CUBIC_COUNT"].ToString()));
                    VisualLocationList.Add(VisualLocation);
                    VisualLocation.InvLotLocLpnInfos = new InvLotLocLpnDA().GetInvLotLocLpnByLoc(VisualLocation.Loc, currentWHName);
                }
            }
            return VisualLocationList;
        }

        public LotTemplateInfo GetAttributeInfoBySkuID(string SkuID, string OwnerID, string whLoginID)
        {
            string sqlStr = "SELECT ROWID, WH_ID, LOT_TEMPLATE_ID, DESCRIPTION, PRODUCE_DATE_VISIBLE, PRODUCE_DATE_MANDATORY, EXPIRY_DATE_VISIBLE, EXPIRY_DATE_MANDATORY, EXTERNAL_LOT_VISIBLE, EXTERNAL_LOT_MANDATORY, LOT_ATTR01_LABEL, LOT_ATTR02_LABEL, LOT_ATTR03_LABEL, LOT_ATTR04_LABEL, LOT_ATTR05_LABEL, LOT_ATTR06_LABEL, LOT_ATTR07_LABEL, LOT_ATTR08_LABEL, LOT_ATTR09_LABEL, LOT_ATTR10_LABEL, PRODUCE_DATE_LABEL, EXPIRY_DATE_LABEL, EXTERNAL_LOT_LABEL, LOT_ATTR01_VISIBLE, LOT_ATTR02_VISIBLE, LOT_ATTR03_VISIBLE, LOT_ATTR04_VISIBLE, LOT_ATTR05_VISIBLE, LOT_ATTR06_VISIBLE, LOT_ATTR07_VISIBLE, LOT_ATTR08_VISIBLE, LOT_ATTR09_VISIBLE, LOT_ATTR10_VISIBLE, LOT_ATTR01_MANDATORY, LOT_ATTR02_MANDATORY, LOT_ATTR03_MANDATORY, LOT_ATTR04_MANDATORY, LOT_ATTR05_MANDATORY, LOT_ATTR06_MANDATORY, LOT_ATTR07_MANDATORY, LOT_ATTR08_MANDATORY, LOT_ATTR09_MANDATORY, LOT_ATTR10_MANDATORY, LOT_ATTR01_TYPE, LOT_ATTR02_TYPE, LOT_ATTR03_TYPE, LOT_ATTR04_TYPE, LOT_ATTR05_TYPE, LOT_ATTR06_TYPE, LOT_ATTR07_TYPE, LOT_ATTR08_TYPE, LOT_ATTR09_TYPE, LOT_ATTR10_TYPE, LOT_ATTR01_VALUE, LOT_ATTR02_VALUE, LOT_ATTR03_VALUE, LOT_ATTR04_VALUE, LOT_ATTR05_VALUE, LOT_ATTR06_VALUE, LOT_ATTR07_VALUE, LOT_ATTR08_VALUE, LOT_ATTR09_VALUE, LOT_ATTR10_VALUE, LOT_ATTR01_SEQNO, LOT_ATTR02_SEQNO, LOT_ATTR03_SEQNO, LOT_ATTR04_SEQNO, LOT_ATTR05_SEQNO, LOT_ATTR06_SEQNO, LOT_ATTR07_SEQNO, LOT_ATTR08_SEQNO, LOT_ATTR09_SEQNO, LOT_ATTR10_SEQNO, PRODUCE_DATE_SEQNO, EXPIRY_DATE_SEQNO, EXTERNAL_LOT_SEQNO, IS_ACTIVE, SYSTEM_CREATED, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, RF_LOT_ATTR01_VISIBLE, RF_LOT_ATTR02_VISIBLE, RF_LOT_ATTR03_VISIBLE, RF_LOT_ATTR04_VISIBLE, RF_LOT_ATTR05_VISIBLE, RF_LOT_ATTR06_VISIBLE, RF_LOT_ATTR07_VISIBLE, RF_LOT_ATTR08_VISIBLE, RF_LOT_ATTR09_VISIBLE, RF_LOT_ATTR10_VISIBLE, RF_PRODUCE_DATE_VISIBLE, RF_EXPIRY_DATE_VISIBLE, RF_EXTERNAL_LOT_VISIBLE, LOT_ATTR01_MANDATORY_OT, LOT_ATTR02_MANDATORY_OT, LOT_ATTR03_MANDATORY_OT, LOT_ATTR04_MANDATORY_OT, LOT_ATTR05_MANDATORY_OT, LOT_ATTR06_MANDATORY_OT, LOT_ATTR07_MANDATORY_OT, LOT_ATTR08_MANDATORY_OT, LOT_ATTR09_MANDATORY_OT, LOT_ATTR10_MANDATORY_OT, PRODUCE_DATE_MANDATORY_OT, EXPIRY_DATE_MANDATORY_OT, EXTERNAL_LOT_MANDATORY_OT, LOT_ATTR01_DEFAULT_IN, LOT_ATTR02_DEFAULT_IN, LOT_ATTR03_DEFAULT_IN, LOT_ATTR04_DEFAULT_IN, LOT_ATTR05_DEFAULT_IN, LOT_ATTR06_DEFAULT_IN, LOT_ATTR07_DEFAULT_IN, LOT_ATTR08_DEFAULT_IN, LOT_ATTR09_DEFAULT_IN, LOT_ATTR10_DEFAULT_IN, LOT_ATTR01_DEFAULT_OT, LOT_ATTR02_DEFAULT_OT, LOT_ATTR03_DEFAULT_OT, LOT_ATTR04_DEFAULT_OT, LOT_ATTR05_DEFAULT_OT, LOT_ATTR06_DEFAULT_OT, LOT_ATTR07_DEFAULT_OT, LOT_ATTR08_DEFAULT_OT, LOT_ATTR09_DEFAULT_OT, LOT_ATTR10_DEFAULT_OT FROM @_@LOT_TEMPLATE WHERE LOT_TEMPLATE_ID IN ( SELECT LOT_TEMPLATE_ID FROM @_@SKU WHERE OWNER_ID = @OWNERID AND SKU_ID = @SKUID) AND IS_ACTIVE = 'Y' ".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@SkuID", SkuID), new SqlParameter("@OwnerID", OwnerID) };
            LotTemplateInfo lotTemplate = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sqlStr, paras))
            {
                if (reader.Read())
                {
                    lotTemplate = new LotTemplateInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT_TEMPLATE_ID"].ToString(), reader["DESCRIPTION"].ToString(), reader["PRODUCE_DATE_VISIBLE"].ToString(), reader["PRODUCE_DATE_MANDATORY"].ToString(), reader["EXPIRY_DATE_VISIBLE"].ToString(), reader["EXPIRY_DATE_MANDATORY"].ToString(), reader["EXTERNAL_LOT_VISIBLE"].ToString(), reader["EXTERNAL_LOT_MANDATORY"].ToString(), reader["LOT_ATTR01_LABEL"].ToString(), reader["LOT_ATTR02_LABEL"].ToString(), reader["LOT_ATTR03_LABEL"].ToString(), reader["LOT_ATTR04_LABEL"].ToString(), reader["LOT_ATTR05_LABEL"].ToString(), reader["LOT_ATTR06_LABEL"].ToString(), reader["LOT_ATTR07_LABEL"].ToString(), reader["LOT_ATTR08_LABEL"].ToString(), reader["LOT_ATTR09_LABEL"].ToString(), reader["LOT_ATTR10_LABEL"].ToString(), reader["PRODUCE_DATE_LABEL"].ToString(), reader["EXPIRY_DATE_LABEL"].ToString(), reader["EXTERNAL_LOT_LABEL"].ToString(), reader["LOT_ATTR01_VISIBLE"].ToString(), reader["LOT_ATTR02_VISIBLE"].ToString(), reader["LOT_ATTR03_VISIBLE"].ToString(), reader["LOT_ATTR04_VISIBLE"].ToString(), reader["LOT_ATTR05_VISIBLE"].ToString(), reader["LOT_ATTR06_VISIBLE"].ToString(), reader["LOT_ATTR07_VISIBLE"].ToString(), reader["LOT_ATTR08_VISIBLE"].ToString(), reader["LOT_ATTR09_VISIBLE"].ToString(), reader["LOT_ATTR10_VISIBLE"].ToString(), reader["LOT_ATTR01_MANDATORY"].ToString(), reader["LOT_ATTR02_MANDATORY"].ToString(), reader["LOT_ATTR03_MANDATORY"].ToString(), reader["LOT_ATTR04_MANDATORY"].ToString(), reader["LOT_ATTR05_MANDATORY"].ToString(), reader["LOT_ATTR06_MANDATORY"].ToString(), reader["LOT_ATTR07_MANDATORY"].ToString(), reader["LOT_ATTR08_MANDATORY"].ToString(), reader["LOT_ATTR09_MANDATORY"].ToString(), reader["LOT_ATTR10_MANDATORY"].ToString(), reader["LOT_ATTR01_TYPE"].ToString(), reader["LOT_ATTR02_TYPE"].ToString(), reader["LOT_ATTR03_TYPE"].ToString(), reader["LOT_ATTR04_TYPE"].ToString(), reader["LOT_ATTR05_TYPE"].ToString(), reader["LOT_ATTR06_TYPE"].ToString(), reader["LOT_ATTR07_TYPE"].ToString(), reader["LOT_ATTR08_TYPE"].ToString(), reader["LOT_ATTR09_TYPE"].ToString(), reader["LOT_ATTR10_TYPE"].ToString(), reader["LOT_ATTR01_VALUE"].ToString(), reader["LOT_ATTR02_VALUE"].ToString(), reader["LOT_ATTR03_VALUE"].ToString(), reader["LOT_ATTR04_VALUE"].ToString(), reader["LOT_ATTR05_VALUE"].ToString(), reader["LOT_ATTR06_VALUE"].ToString(), reader["LOT_ATTR07_VALUE"].ToString(), reader["LOT_ATTR08_VALUE"].ToString(), reader["LOT_ATTR09_VALUE"].ToString(), reader["LOT_ATTR10_VALUE"].ToString(), (reader["LOT_ATTR01_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR01_SEQNO"].ToString()) : 0, (reader["LOT_ATTR02_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR02_SEQNO"].ToString()) : 0, (reader["LOT_ATTR03_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR03_SEQNO"].ToString()) : 0, (reader["LOT_ATTR04_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR04_SEQNO"].ToString()) : 0, (reader["LOT_ATTR05_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR05_SEQNO"].ToString()) : 0, (reader["LOT_ATTR06_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR06_SEQNO"].ToString()) : 0, (reader["LOT_ATTR07_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR07_SEQNO"].ToString()) : 0, (reader["LOT_ATTR08_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR08_SEQNO"].ToString()) : 0, (reader["LOT_ATTR09_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR09_SEQNO"].ToString()) : 0, (reader["LOT_ATTR10_SEQNO"].ToString() != "") ? int.Parse(reader["LOT_ATTR10_SEQNO"].ToString()) : 0, (reader["PRODUCE_DATE_SEQNO"].ToString() != "") ? int.Parse(reader["PRODUCE_DATE_SEQNO"].ToString()) : 0, (reader["EXPIRY_DATE_SEQNO"].ToString() != "") ? int.Parse(reader["EXPIRY_DATE_SEQNO"].ToString()) : 0, (reader["EXTERNAL_LOT_SEQNO"].ToString() != "") ? int.Parse(reader["EXTERNAL_LOT_SEQNO"].ToString()) : 0, reader["IS_ACTIVE"].ToString(), reader["SYSTEM_CREATED"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["RF_LOT_ATTR01_VISIBLE"].ToString(), reader["RF_LOT_ATTR02_VISIBLE"].ToString(), reader["RF_LOT_ATTR03_VISIBLE"].ToString(), reader["RF_LOT_ATTR04_VISIBLE"].ToString(), reader["RF_LOT_ATTR05_VISIBLE"].ToString(), reader["RF_LOT_ATTR06_VISIBLE"].ToString(), reader["RF_LOT_ATTR07_VISIBLE"].ToString(), reader["RF_LOT_ATTR08_VISIBLE"].ToString(), reader["RF_LOT_ATTR09_VISIBLE"].ToString(), reader["RF_LOT_ATTR10_VISIBLE"].ToString(), reader["RF_PRODUCE_DATE_VISIBLE"].ToString(), reader["RF_EXPIRY_DATE_VISIBLE"].ToString(), reader["RF_EXTERNAL_LOT_VISIBLE"].ToString(), reader["LOT_ATTR01_MANDATORY_OT"].ToString(), reader["LOT_ATTR02_MANDATORY_OT"].ToString(), reader["LOT_ATTR03_MANDATORY_OT"].ToString(), reader["LOT_ATTR04_MANDATORY_OT"].ToString(), reader["LOT_ATTR05_MANDATORY_OT"].ToString(), reader["LOT_ATTR06_MANDATORY_OT"].ToString(), reader["LOT_ATTR07_MANDATORY_OT"].ToString(), reader["LOT_ATTR08_MANDATORY_OT"].ToString(), reader["LOT_ATTR09_MANDATORY_OT"].ToString(), reader["LOT_ATTR10_MANDATORY_OT"].ToString(), reader["PRODUCE_DATE_MANDATORY_OT"].ToString(), reader["EXPIRY_DATE_MANDATORY_OT"].ToString(), reader["EXTERNAL_LOT_MANDATORY_OT"].ToString(), reader["LOT_ATTR01_DEFAULT_IN"].ToString(), reader["LOT_ATTR02_DEFAULT_IN"].ToString(), reader["LOT_ATTR03_DEFAULT_IN"].ToString(), reader["LOT_ATTR04_DEFAULT_IN"].ToString(), reader["LOT_ATTR05_DEFAULT_IN"].ToString(), reader["LOT_ATTR06_DEFAULT_IN"].ToString(), reader["LOT_ATTR07_DEFAULT_IN"].ToString(), reader["LOT_ATTR08_DEFAULT_IN"].ToString(), reader["LOT_ATTR09_DEFAULT_IN"].ToString(), reader["LOT_ATTR10_DEFAULT_IN"].ToString(), reader["LOT_ATTR01_DEFAULT_OT"].ToString(), reader["LOT_ATTR02_DEFAULT_OT"].ToString(), reader["LOT_ATTR03_DEFAULT_OT"].ToString(), reader["LOT_ATTR04_DEFAULT_OT"].ToString(), reader["LOT_ATTR05_DEFAULT_OT"].ToString(), reader["LOT_ATTR06_DEFAULT_OT"].ToString(), reader["LOT_ATTR07_DEFAULT_OT"].ToString(), reader["LOT_ATTR08_DEFAULT_OT"].ToString(), reader["LOT_ATTR09_DEFAULT_OT"].ToString(), reader["LOT_ATTR10_DEFAULT_OT"].ToString());
                }
            }
            return lotTemplate;
        }

        public decimal GetAvailableQty(string OwnerID, string SkuID, string whLoginID)
        {
            string SQL_GETAVAILABLEQTY = "SELECT SUM(QTY-PREALLOCATED_QTY-ALLOCATED_QTY-PICKED_QTY-ONHOLD_QTY) AS AVAILABLEQTY FROM @_@INV_LOT WHERE OWNER_ID = @OWNERID AND SKU_ID = @SKUID ";
            SQL_GETAVAILABLEQTY = SQL_GETAVAILABLEQTY.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNERID", OwnerID), new SqlParameter("@SKUID", SkuID) };
            object temp = null;
            temp = DBHelper.ExecuteScalar(CommandType.Text, SQL_GETAVAILABLEQTY, paras);
            if (temp != DBNull.Value)
            {
                return Convert.ToDecimal(temp);
            }
            return 0M;
        }

        public List<SysCode4DDL> GetBaseInfoForDDLSource(string sourceFrom, string whLoginID)
        {
            string sql = string.Empty;
            string CS40001 = sourceFrom;
            if (CS40001 != null)
            {
                if (!(CS40001 == "DEPARTMENT"))
                {
                    if (CS40001 == "WORK_CLASS")
                    {
                        sql = " SELECT WORK_CLASS_ID AS SysCodeValue,DESCR AS SysCodeText FROM @_@WORK_CLASS ".Replace("@_@", whLoginID);
                    }
                    else if (CS40001 == "EMPLOYEE")
                    {
                        sql = " SELECT EMPLOYEE_ID AS SysCodeValue,EMPLOYEE_NAME AS SysCodeText FROM @_@EMPLOYEE ".Replace("@_@", whLoginID);
                    }
                    else if (CS40001 == "EQUIPMENT_POR_FILE")
                    {
                        sql = " SELECT EQUIPMENT_PROFILE_ID AS SysCodeValue,DESCR AS SysCodeText FROM @_@EQUIPMENT_PROFILE ".Replace("@_@", whLoginID);
                    }
                }
                else
                {
                    sql = " SELECT DEPARTMENT_ID AS SysCodeValue,DESCR AS SysCodeText FROM @_@DEPARTMENT ".Replace("@_@", whLoginID);
                }
            }
            List<SysCode4DDL> sysCode4DDLS = new List<SysCode4DDL>();
            SysCode4DDL sysCode = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    sysCode = new SysCode4DDL(reader["SysCodeValue"].ToString(), reader["SysCodeText"].ToString());
                    sysCode4DDLS.Add(sysCode);
                }
            }
            return sysCode4DDLS;
        }

        public List<CartonGroup4DDL> GetCartonGroup4Select(string whLoginID)
        {
            string SQL_SELECT_CARTON_GROUP = " SELECT DISTINCT CARTON_GROUP AS CARTON_GROUP, CARTON_GROUP AS DESCRIPTION FROM @_@CARTON ";
            SQL_SELECT_CARTON_GROUP = SQL_SELECT_CARTON_GROUP.Replace("@_@", whLoginID);
            List<CartonGroup4DDL> cartonGroupList = new List<CartonGroup4DDL>();
            CartonGroup4DDL cartonGroup = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, SQL_SELECT_CARTON_GROUP))
            {
                while (reader.Read())
                {
                    cartonGroup = new CartonGroup4DDL(reader["CARTON_GROUP"].ToString(), reader["DESCRIPTION"].ToString());
                    cartonGroupList.Add(cartonGroup);
                }
            }
            return cartonGroupList;
        }

        public DataSet GetCurrentUserAndWHCount()
        {
            string strSQL = "SELECT (SELECT COUNT(*) FROM SYSADM.USER_MST) AS USER_COUNT,(SELECT COUNT(*)-1 FROM SYSADM.DB_CONFIG) AS WH_COUNT";
            return DBHelper.ExecuteDataSet(CommandType.Text, strSQL);
        }

        public List<CycleCountRule4DDL> GetCycleCountRule4Select(string whLoginID)
        {
            string SQL_SELECT_CYCLECOUNTRULE = "SELECT DISTINCT CC_ADJ_RULE_ID, DESCR  FROM @_@CYCLE_COUNT_RULE";
            SQL_SELECT_CYCLECOUNTRULE = SQL_SELECT_CYCLECOUNTRULE.Replace("@_@", whLoginID);
            List<CycleCountRule4DDL> cycleCountRule4DDLList = new List<CycleCountRule4DDL>();
            CycleCountRule4DDL cycleCountRule4DDL = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, SQL_SELECT_CYCLECOUNTRULE))
            {
                while (reader.Read())
                {
                    cycleCountRule4DDL = new CycleCountRule4DDL(reader["CC_ADJ_RULE_ID"].ToString(), reader["DESCR"].ToString());
                    cycleCountRule4DDLList.Add(cycleCountRule4DDL);
                }
            }
            return cycleCountRule4DDLList;
        }

        public List<string> GetDataBaseID(string whDbId)
        {
            string sql = string.Empty;
            List<string> whID = null;
            DataSet allDataBaseID = null;
            if (whDbId.ToString().Trim() == ConstUtils.CONST_GLOBAL_SETTING)
            {
                sql = "SELECT DB_LOGIN_ID FROM SYSADM.DB_CONFIG WHERE IS_GLOBAL = 'N' AND IS_ACTIVE = 'Y'";
                if (allDataBaseID == null)
                {
                    allDataBaseID = new DataSet();
                    allDataBaseID = DBHelper.ExecuteDataSet(CommandType.Text, sql);
                }
                if ((allDataBaseID != null) && (allDataBaseID.Tables[0].Rows.Count > 0))
                {
                    whID = new List<string>();
                    for (int i = 0; i < allDataBaseID.Tables[0].Rows.Count; i++)
                    {
                        whID.Add(allDataBaseID.Tables[0].Rows[i]["DB_LOGIN_ID"].ToString().Trim() + ".");
                    }
                }
                return whID;
            }
            sql = "SELECT DB_LOGIN_ID FROM SYSADM.DB_CONFIG WHERE WH_DB_ID = @WH_DB_ID  AND IS_ACTIVE = 'Y'";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", whDbId) };
            object obj = DBHelper.ExecuteScalar(CommandType.Text, sql, paras);
            if (obj != null)
            {
                whID = new List<string>();
                whID.Add(obj.ToString().Trim() + ".");
            }
            return whID;
        }

        public List<string> GetDataBaseID(DataBase dataBase, DbTransaction tran, string whDbId)
        {
            string sql = string.Empty;
            List<string> whID = null;
            DataSet allDataBaseID = null;
            if (whDbId.ToString().Trim() == ConstUtils.CONST_GLOBAL_SETTING)
            {
                sql = "SELECT DB_LOGIN_ID FROM SYSADM.DB_CONFIG WHERE IS_GLOBAL = 'N' AND IS_ACTIVE = 'Y'";
                if (allDataBaseID == null)
                {
                    allDataBaseID = new DataSet();
                    allDataBaseID = dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
                }
                if ((allDataBaseID != null) && (allDataBaseID.Tables[0].Rows.Count > 0))
                {
                    whID = new List<string>();
                    for (int i = 0; i < allDataBaseID.Tables[0].Rows.Count; i++)
                    {
                        whID.Add(allDataBaseID.Tables[0].Rows[i]["DB_LOGIN_ID"].ToString().Trim() + ".");
                    }
                }
                return whID;
            }
            sql = "SELECT DB_LOGIN_ID FROM SYSADM.DB_CONFIG WHERE WH_DB_ID = @WH_DB_ID AND IS_ACTIVE = 'Y'";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", whDbId) };
            object obj = dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras);
            if (obj != null)
            {
                whID = new List<string>();
                whID.Add(obj.ToString().Trim() + ".");
            }
            return whID;
        }

        public string GetDataBaseIDByWhDBName(string whDbName)
        {
            string sql = "SELECT DB_LOGIN_ID FROM SYSADM.DB_CONFIG WHERE WH_DB_ID = @WH_DB_ID AND IS_ACTIVE = 'Y'";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WH_DB_ID", whDbName) };
            object obj = DBHelper.ExecuteScalar(CommandType.Text, sql, paras);
            if (obj != null)
            {
                return (obj.ToString() + ".");
            }
            return "";
        }

        public List<FieldNameInfo> GetFieldNameByTableName(string TableName, string whLoginID)
        {
            string sql = ("SP_HELP[@_@" + TableName + "]").Replace("@_@", whLoginID);
            List<FieldNameInfo> fieldNameList = new List<FieldNameInfo>();
            using (DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql))
            {
                if ((ds == null) || (ds.Tables.Count <= 0))
                {
                    return fieldNameList;
                }
                if (ds.Tables[1].Rows.Count <= 0)
                {
                    return fieldNameList;
                }
                for (int i = 0; ds.Tables[1].Rows.Count > i; i++)
                {
                    FieldNameInfo fieldNameInfo = new FieldNameInfo(ds.Tables[1].Rows[i]["Column_name"].ToString(), ds.Tables[1].Rows[i]["Type"].ToString(), ds.Tables[1].Rows[i]["Length"].ToString());
                    fieldNameList.Add(fieldNameInfo);
                }
            }
            return fieldNameList;
        }

        public DataSet GetInvSkuQtyByOwnerID(string ownerID, string whLoginID)
        {
            ownerID = ownerID.Replace(",", "','");
            string strSql = "SELECT IL.SKU_ID,SUM(IL.QTY) AS QTY,SUM(IL.QTY-IL.PREALLOCATED_QTY-IL.ALLOCATED_QTY-IL.PICKED_QTY) AS AVAILABLE_QTY,LA.LOT_ATTR01 FROM  " + whLoginID + "INV_LOT IL LEFT JOIN WH1.LOT_ATTRIBUTE LA ON IL.LOT = LA.LOT WHERE IL.STATUS ='OK' AND IL.OWNER_ID IN ('" + ownerID + "') GROUP BY IL.SKU_ID,LA.LOT_ATTR01";
            return DBHelper.ExecuteDataSet(CommandType.Text, strSql);
        }

        public List<LotAttributeCodeInfo> GetLotAttributCodeByGroupID(string groupID, string whLoginID)
        {
            string sql = "SELECT ROWID, WH_ID, GROUP_ID, CODE_ID, SEQ_NO, DESCR, DESCR2, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE FROM @_@LOT_ATTRIBUTE_CODE WHERE GROUP_ID = @GROUPID AND IS_ACTIVE = 'Y' ORDER BY SEQ_NO ASC ".Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@GROUPID", groupID) };
            List<LotAttributeCodeInfo> lotAttributeCodeInfos = new List<LotAttributeCodeInfo>();
            LotAttributeCodeInfo lotAttributeCode = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    lotAttributeCode = new LotAttributeCodeInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["GROUP_ID"].ToString(), reader["CODE_ID"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    lotAttributeCodeInfos.Add(lotAttributeCode);
                }
            }
            return lotAttributeCodeInfos;
        }

        public List<string> GetLotAttributeCode4LotTemplate(string whLoginID)
        {
            string sql = "SELECT DISTINCT GROUP_ID FROM @_@LOT_ATTRIBUTE_CODE ";
            sql = sql.Replace("@_@", whLoginID);
            List<string> codes = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    codes.Add(reader["GROUP_ID"].ToString());
                }
            }
            return codes;
        }

        public List<LotTemplate4DDL> GetLotTemplate4Select(string whLoginID)
        {
            string SQL_SELECT_LOT_TEMPLATE = " SELECT LOT_TEMPLATE_ID,DESCRIPTION FROM @_@LOT_TEMPLATE  ";
            SQL_SELECT_LOT_TEMPLATE = SQL_SELECT_LOT_TEMPLATE.Replace("@_@", whLoginID);
            List<LotTemplate4DDL> lotTemplateList = new List<LotTemplate4DDL>();
            LotTemplate4DDL lotTemplate = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, SQL_SELECT_LOT_TEMPLATE))
            {
                while (reader.Read())
                {
                    lotTemplate = new LotTemplate4DDL(reader["LOT_TEMPLATE_ID"].ToString(), reader["DESCRIPTION"].ToString());
                    lotTemplateList.Add(lotTemplate);
                }
            }
            return lotTemplateList;
        }

        public int GetMaxLineID(string tableName, string id, string whLoginID)
        {
            string strSql = "SELECT MAX(@) FROM " + whLoginID + tableName;
            string CS40001 = tableName;
            if (CS40001 != null)
            {
                if (!(CS40001 == "RECEIPT_DETAIL"))
                {
                    if (CS40001 == "RECEIPT_DETAIL_LPN")
                    {
                        strSql = strSql.Replace("@", "RECEIPT_LINE_ID") + " WHERE RECEIPT_ID='" + id + "'";
                    }
                    else if (CS40001 == "ORDER_DETAIL")
                    {
                        strSql = strSql.Replace("@", "LINE_ID") + " WHERE ORDER_ID='" + id + "'";
                    }
                    else if (CS40001 == "PURCHASE_ORDER_DETAIL")
                    {
                        strSql = strSql.Replace("@", "LINE_ID") + " WHERE PO_ID='" + id + "'";
                    }
                    else if (CS40001 == "MOVEMENT_DETAIL")
                    {
                        strSql = strSql.Replace("@", "LINE_ID") + " WHERE MOVEMENT_ID='" + id + "'";
                    }
                }
                else
                {
                    strSql = strSql.Replace("@", "RECEIPT_LINE_ID") + " WHERE RECEIPT_ID='" + id + "'";
                }
            }
            object MaxLine = DBHelper.ExecuteScalar(CommandType.Text, strSql);
            if (string.IsNullOrEmpty(MaxLine.ToString()))
            {
                return 0;
            }
            return (int) MaxLine;
        }

        public int GetMaxLineID(DataBase dataBase, DbTransaction tran, string tableName, string id, string whLoginID)
        {
            string strSql = "SELECT MAX(@) FROM " + whLoginID + tableName;
            string CS40001 = tableName;
            if (CS40001 != null)
            {
                if (!(CS40001 == "RECEIPT_DETAIL"))
                {
                    if (CS40001 == "ORDER_DETAIL")
                    {
                        strSql = strSql.Replace("@", "LINE_ID") + " WHERE ORDER_ID='" + id + "'";
                    }
                    else if (CS40001 == "PURCHASE_ORDER_DETAIL")
                    {
                        strSql = strSql.Replace("@", "LINE_ID") + " WHERE PO_ID='" + id + "'";
                    }
                }
                else
                {
                    strSql = strSql.Replace("@", "RECEIPT_LINE_ID") + " WHERE RECEIPT_ID='" + id + "'";
                }
            }
            object MaxLine = dataBase.ExecuteScalar(tran, CommandType.Text, strSql);
            if (string.IsNullOrEmpty(MaxLine.ToString()))
            {
                return 0;
            }
            return (int) MaxLine;
        }

        public List<string> GetOrderIDLineIDByPickDetailID(List<string> PickDetailID, string whLoginID)
        {
            List<string> newPickDetailID = new List<string>();
            if ((PickDetailID != null) && (PickDetailID.Count > 0))
            {
                string sql = "SELECT ORDER_ID+'/'+CAST(ORDER_LINE_ID AS VARCHAR(30))+'/'+PICK_DETAIL_ID AS PICK_DETAIL_ID FROM @_@PICK_DETAIL WHERE PICK_DETAIL_ID IN (@PICK_DETAIL_ID)";
                sql = sql.Replace("@_@", whLoginID);
                string tmpPID = string.Empty;
                for (int i = 1; i < PickDetailID.Count; i++)
                {
                    tmpPID = tmpPID + "','" + PickDetailID[i];
                }
                tmpPID = "'" + tmpPID + "'";
                sql = sql.Replace("@PICK_DETAIL_ID", tmpPID);
                using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql))
                {
                    while (reader.Read())
                    {
                        newPickDetailID.Add(reader["PICK_DETAIL_ID"].ToString());
                    }
                }
            }
            return newPickDetailID;
        }

        public List<PreferenceValueInfo> GetPreferenceValueByID(string preferenceID)
        {
            string SQL_SELECT_ALL_PREFERENCE_VALUE = " SELECT  ROWID, PREFERENCE_ID, LINE_ID, PREF_VAL_VALUE, DESCRIPTION, IS_DISPLAY_DEFAULT, SYSTEM_CREATED, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE FROM SYSADM.PREFERENCE_VALUE ";
            string sql = SQL_SELECT_ALL_PREFERENCE_VALUE + " WHERE PREFERENCE_ID = @PREFERENCE_ID ORDER BY LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PREFERENCE_ID", preferenceID) };
            List<PreferenceValueInfo> preferenceValueInfos = new List<PreferenceValueInfo>();
            PreferenceValueInfo preferenceValueInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    preferenceValueInfo = new PreferenceValueInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["PREFERENCE_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["PREF_VAL_VALUE"].ToString(), reader["DESCRIPTION"].ToString(), reader["IS_DISPLAY_DEFAULT"].ToString(), reader["SYSTEM_CREATED"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    preferenceValueInfos.Add(preferenceValueInfo);
                }
            }
            return preferenceValueInfos;
        }

        public List<ReceiptValidation4DDL> GetReceiptValidation4Select(string whLoginID)
        {
            string SQL_SELECT_RECEIPTVALIDATION = "SELECT DISTINCT RECEIPT_VALIDATION_ID AS RECEIPTVALIDATIONID, DESCR AS DESCRIPTION FROM @_@RECEIPT_VALIDATION";
            SQL_SELECT_RECEIPTVALIDATION = SQL_SELECT_RECEIPTVALIDATION.Replace("@_@", whLoginID);
            List<ReceiptValidation4DDL> receiptValidation4DDLList = new List<ReceiptValidation4DDL>();
            ReceiptValidation4DDL ReceiptValidation = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, SQL_SELECT_RECEIPTVALIDATION))
            {
                while (reader.Read())
                {
                    ReceiptValidation = new ReceiptValidation4DDL(reader["RECEIPTVALIDATIONID"].ToString(), reader["DESCRIPTION"].ToString());
                    receiptValidation4DDLList.Add(ReceiptValidation);
                }
            }
            return receiptValidation4DDLList;
        }

        public DataSet GetRelatedOrders(string Type, string skuID, string ownerID, string lot, string loc, string lpn, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            string CS40001 = Type.ToUpper();
            if (CS40001 != null)
            {
                if (!(CS40001 == "PREALLOCATED"))
                {
                    if (CS40001 == "ALLOCATED")
                    {
                        strSql.AppendFormat(" SELECT ORDER_ID,SUM(QTY) AS QTY FROM {0}PICK_DETAIL WHERE STATUS='New' AND SKU_ID='{1}' AND OWNER_ID='{2}'", whLoginID, skuID, ownerID);
                        if (!(string.IsNullOrEmpty(lot) || !(lot.Trim() != "&nbsp;")))
                        {
                            strSql.AppendFormat(" AND LOT='{0}'", lot);
                        }
                        if (!(string.IsNullOrEmpty(loc) || !(loc.Trim() != "&nbsp;")))
                        {
                            strSql.AppendFormat(" AND LOC='{0}'", loc);
                        }
                        if (!(string.IsNullOrEmpty(lpn) || !(lpn.Trim() != "&nbsp;")))
                        {
                            strSql.AppendFormat(" AND LPN_ID='{0}'", lpn);
                        }
                        strSql.Append(" GROUP BY ORDER_ID ");
                    }
                    else if (CS40001 == "PICKED")
                    {
                        strSql.AppendFormat(" SELECT ORDER_ID,QTY FROM {0}PICK_DETAIL WHERE STATUS='Picked' AND SKU_ID='{1}' AND OWNER_ID='{2}' AND LOT='{3}' AND LOC='{4}' AND LPN_ID='{5}'", new object[] { whLoginID, skuID, ownerID, lot, loc, lpn });
                    }
                }
                else
                {
                    strSql.AppendFormat(" SELECT ORDER_ID,SUM(QTY) AS QTY FROM {0}PREALLOCATE_PICK_DETAIL WHERE QTY > 0 AND OWNER_ID='{1}' AND SKU_ID='{2}' ", whLoginID, ownerID, skuID);
                    if (!(string.IsNullOrEmpty(lot) || !(lot.Trim() != "&nbsp;")))
                    {
                        strSql.AppendFormat(" AND LOT='{0}'", lot);
                    }
                    strSql.Append(" GROUP BY ORDER_ID ");
                }
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public List<Rule4DDL> GetRules4Select(string ruleID, string whLoginID)
        {
            string putawayRuleSql = "SELECT PUTAWAY_RULE_ID,DESCR FROM @_@PUTAWAY_RULE  ";
            string rotationRuleSql = "SELECT ROTATION_RULE_ID,DESCR FROM @_@ROTATION_RULE ";
            string preallocateRuleSql = "SELECT PREALLOCATE_RULE_ID,DESCR FROM @_@PREALLOCATE_RULE  ";
            string allocateRuleSql = "SELECT ALLOCATE_RULE_ID,DESCR FROM @_@ALLOCATE_RULE  ";
            string taskRuleSql = "SELECT TASK_RULE_ID,DESCR FROM @_@TASK_RULE ";
            string sql = string.Empty;
            string CS40001 = ruleID.ToUpper();
            if (CS40001 != null)
            {
                if (!(CS40001 == "PUTAWAYRULE"))
                {
                    if (CS40001 == "ROTATIONRULE")
                    {
                        sql = rotationRuleSql;
                    }
                    else if (CS40001 == "PREALLOCATERULE")
                    {
                        sql = preallocateRuleSql;
                    }
                    else if (CS40001 == "ALLOCATERULE")
                    {
                        sql = allocateRuleSql;
                    }
                    else if (CS40001 == "TASKRULE")
                    {
                        sql = taskRuleSql;
                    }
                }
                else
                {
                    sql = putawayRuleSql;
                }
            }
            sql = sql.Replace("@_@", whLoginID);
            List<Rule4DDL> rule4DDLS = new List<Rule4DDL>();
            Rule4DDL rule = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    rule = new Rule4DDL(reader[0].ToString(), reader[1].ToString());
                    rule4DDLS.Add(rule);
                }
            }
            return rule4DDLS;
        }

        public DataSet GetShowGridviewInfo(string functionID, string gridviewID, string whLoginID, PubParasInfo pubParasInfo)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append(" IF EXISTS(SELECT 1 FROM sysadm.CUST_GRIDVIEW WHERE FUNCTION_ID = @FUNCTION_ID AND GIRDVIEW_ID = @GRIDVIEW_ID AND [USER_ID] = @USER_ID) ");
            sbSQL.Append(" BEGIN ");
            sbSQL.Append("   SELECT cg.ROWID, cg.COLUMN_INDEX, cg.SEQ_NO, cg.HEADER_TEXT, cg.FOOTER_TEXT, cg.WIDTH, ig.DATA_FIELD ");
            sbSQL.Append("   FROM sysadm.CUST_GRIDVIEW cg ");
            sbSQL.Append("     LEFT JOIN sysadm.INIT_GRIDVIEW ig ON ig.FUNCTION_ID = cg.FUNCTION_ID AND ig.GRIDVIEW_ID = cg.GIRDVIEW_ID AND ig.COLUMN_INDEX = cg.COLUMN_INDEX ");
            sbSQL.Append("   WHERE ISNULL(ig.DATA_FIELD,'') <> '' AND cg.FUNCTION_ID = @FUNCTION_ID AND cg.GIRDVIEW_ID = @GRIDVIEW_ID AND cg.[USER_ID] = @USER_ID ");
            sbSQL.Append("   ORDER BY cg.SEQ_NO ");
            sbSQL.Append(" END ");
            sbSQL.Append(" ELSE ");
            sbSQL.Append(" BEGIN ");
            sbSQL.Append("   SELECT ig.ROWID, ig.COLUMN_INDEX, ig.COLUMN_INDEX AS SEQ_NO, ig.HEADER_TEXT, ig.FOOTER_TEXT, ig.WIDTH, ig.DATA_FIELD ");
            sbSQL.Append("   FROM sysadm.INIT_GRIDVIEW ig ");
            sbSQL.Append("   WHERE ISNULL(ig.DATA_FIELD,'') <> '' AND ig.FUNCTION_ID = @FUNCTION_ID AND ig.GRIDVIEW_ID = @GRIDVIEW_ID ");
            sbSQL.Append("   ORDER BY ig.COLUMN_INDEX ");
            sbSQL.Append(" END ");
            string strSQL = sbSQL.Replace("@FUNCTION_ID", "'" + functionID + "'").Replace("@GRIDVIEW_ID", "'" + gridviewID + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'").ToString();
            return DBHelper.ExecuteDataSet(CommandType.Text, strSQL);
        }

        public SkuInfo GetSkuInfoByUserOwner(string skuID, string userID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = " SELECT  SKU.ROWID AS ROWID, SKU.WH_ID AS WH_ID, SKU.OWNER_ID AS OWNER_ID, SKU.SKU_ID AS SKU_ID, SKU.DESCR AS DESCR, SKU.DESCR2 AS DESCR2, SKU.PACK_ID AS PACK_ID, SKU.CARTON_GROUP AS CARTON_GROUP, SKU.LOT_TEMPLATE_ID AS LOT_TEMPLATE_ID, SKU.TARIFF_ID AS TARIFF_ID, SKU.SKU_CLASS_ID AS SKU_CLASS_ID, SKU.HAZMAT_CODE AS HAZMAT_CODE, SKU.CUSTOM_SKU_NO AS CUSTOM_SKU_NO, SKU.COUNTRY_OF_ORIGIN AS COUNTRY_OF_ORIGIN, SKU.SHELF_LIFE_INDICATOR AS SHELF_LIFE_INDICATOR, SKU.SHELF_LIFE_CODE_TYPE AS SHELF_LIFE_CODE_TYPE, SKU.SHELF_LIFE_ON_RECEIVING AS SHELF_LIFE_ON_RECEIVING, SKU.SHELF_LIFE AS SHELF_LIFE, SKU.DAYS_TO_EXPIRE AS DAYS_TO_EXPIRE, SKU.LENGTH AS LENGTH, SKU.WIDTH AS WIDTH, SKU.HEIGHT AS HEIGHT, SKU.CUBE AS CUBE, SKU.GROSS_WGT AS GROSS_WGT, SKU.NET_WGT AS NET_WGT, SKU.TARE_WGT AS TARE_WGT, SKU.SKU_SIZE AS SKU_SIZE, SKU.SKU_COLOR AS SKU_COLOR, SKU.SKU_STYLE AS SKU_STYLE, SKU.COST_PRICE AS COST_PRICE, SKU.SALE_PRICE AS SALE_PRICE, SKU.NET_PRICE AS NET_PRICE,SKU.ITEM_NO_ID, SKU.IS_SALE_PROMPTION AS IS_SALE_PROMPTION, SKU.RECEIPT_VALIDATION_TEMPLATE AS RECEIPT_VALIDATION_TEMPLATE, SKU.DEFAULT_IN_UOM AS DEFAULT_IN_UOM, SKU.DEFAULT_OUT_UOM AS DEFAULT_OUT_UOM, SKU.RF_DEFAULT_PACK AS RF_DEFAULT_PACK, SKU.PUTAWAY_RULE_ID AS PUTAWAY_RULE_ID, SKU.PREALLOCATE_RULE_ID AS PREALLOCATE_RULE_ID, SKU.ALLOCATE_RULE_ID AS ALLOCATE_RULE_ID, SKU.ROTATION_RULE_ID AS ROTATION_RULE_ID, SKU.CALCULATE_PUTAWAY_LOCATION AS CALCULATE_PUTAWAY_LOCATION, SKU.RECEIPT_VALIDATION_ID AS RECEIPT_VALIDATION_ID, SKU.ZONE_ID AS ZONE_ID, SKU.PUTAWAY_LOC AS PUTAWAY_LOC, SKU.RECEIPT_INSPECTION_LOC AS RECEIPT_INSPECTION_LOC, SKU.QC_LOC_OUT AS QC_LOC_OUT, SKU.RETURNS_LOC AS RETURNS_LOC, SKU.QC_LOC AS QC_LOC, SKU.MAX_QTY AS MAX_QTY, SKU.MIN_QTY AS MIN_QTY, SKU.REORDER_POINT AS REORDER_POINT, SKU.REORDER_QTY AS REORDER_QTY, SKU.STD_ORDER_COST AS STD_ORDER_COST, SKU.CARRY_COST AS CARRY_COST, SKU.ABC AS ABC, SKU.CYCLE_COUNT_FREQUENCY AS CYCLE_COUNT_FREQUENCY, SKU.LAST_CYCLE_COUNT AS LAST_CYCLE_COUNT, SKU.CC_DISCREPAN_CYRULE AS CC_DISCREPAN_CYRULE, SKU.SKU_GROUP AS SKU_GROUP, SKU.SKU_GROUP2 AS SKU_GROUP2, SKU.TRANSPORT_MODE AS TRANSPORT_MODE, SKU.FREIGHT_CLASS AS FREIGHT_CLASS, SKU.IS_ASSEMBLED AS IS_ASSEMBLED, SKU.UDF1 AS UDF1, SKU.UDF2 AS UDF2, SKU.UDF3 AS UDF3, SKU.UDF4 AS UDF4, SKU.UDF5 AS UDF5, SKU.UDF6 AS UDF6, SKU.UDF7 AS UDF7, SKU.UDF8 AS UDF8, SKU.UDF9 AS UDF9, SKU.UDF10 AS UDF10, SKU.B_UDF1 AS B_UDF1, SKU.B_UDF2 AS B_UDF2, SKU.B_UDF3 AS B_UDF3, SKU.B_UDF4 AS B_UDF4, SKU.B_UDF5 AS B_UDF5, SKU.B_UDF6 AS B_UDF6, SKU.B_UDF7 AS B_UDF7, SKU.B_UDF8 AS B_UDF8, SKU.B_UDF9 AS B_UDF9, SKU.B_UDF10 AS B_UDF10, SKU.REMARK1 AS REMARK1, SKU.REMARK2 AS REMARK2, SKU.IS_ACTIVE AS IS_ACTIVE, SKU.TS AS TS, SKU.CREATED_BY AS CREATED_BY, SKU.CREATED_DATE AS CREATED_DATE, SKU.UPDATED_BY AS UPDATED_BY, SKU.UPDATED_DATE AS UPDATED_DATE FROM @_@SKU SKU ".Replace("@_@", whLoginID) + " WHERE SKU.SKU_ID = @SKU_ID AND SKU.OWNER_ID IN (SELECT OWNER_ID FROM SYSADM.USER_OWNER WHERE USER_ID = @USER_ID) ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID), new SqlParameter("@SKU_ID", skuID) };
            SkuInfo skuInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    skuInfo = new SkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["PACK_ID"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LOT_TEMPLATE_ID"].ToString(), reader["TARIFF_ID"].ToString(), reader["SKU_CLASS_ID"].ToString(), reader["HAZMAT_CODE"].ToString(), reader["CUSTOM_SKU_NO"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["SHELF_LIFE_INDICATOR"].ToString(), reader["SHELF_LIFE_CODE_TYPE"].ToString(), (reader["SHELF_LIFE_ON_RECEIVING"].ToString() != "") ? int.Parse(reader["SHELF_LIFE_ON_RECEIVING"].ToString()) : 0, (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, (reader["DAYS_TO_EXPIRE"].ToString() != "") ? int.Parse(reader["DAYS_TO_EXPIRE"].ToString()) : 0, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["TARE_WGT"].ToString() != "") ? float.Parse(reader["TARE_WGT"].ToString()) : 0f, reader["SKU_SIZE"].ToString(), reader["SKU_COLOR"].ToString(), reader["SKU_STYLE"].ToString(), (reader["COST_PRICE"].ToString() != "") ? decimal.Parse(reader["COST_PRICE"].ToString()) : 0M, (reader["SALE_PRICE"].ToString() != "") ? decimal.Parse(reader["SALE_PRICE"].ToString()) : 0M, (reader["NET_PRICE"].ToString() != "") ? decimal.Parse(reader["NET_PRICE"].ToString()) : 0M, reader["IS_SALE_PROMPTION"].ToString(), reader["RECEIPT_VALIDATION_TEMPLATE"].ToString(), reader["DEFAULT_IN_UOM"].ToString(), reader["DEFAULT_OUT_UOM"].ToString(), reader["RF_DEFAULT_PACK"].ToString(), reader["PUTAWAY_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_RULE_ID"].ToString(), reader["ROTATION_RULE_ID"].ToString(), reader["CALCULATE_PUTAWAY_LOCATION"].ToString(), reader["RECEIPT_VALIDATION_ID"].ToString(), reader["ZONE_ID"].ToString(), reader["PUTAWAY_LOC"].ToString(), reader["RECEIPT_INSPECTION_LOC"].ToString(), reader["QC_LOC_OUT"].ToString(), reader["RETURNS_LOC"].ToString(), reader["QC_LOC"].ToString(), (reader["MAX_QTY"].ToString() != "") ? decimal.Parse(reader["MAX_QTY"].ToString()) : 0M, (reader["MIN_QTY"].ToString() != "") ? decimal.Parse(reader["MIN_QTY"].ToString()) : 0M, (reader["REORDER_POINT"].ToString() != "") ? int.Parse(reader["REORDER_POINT"].ToString()) : 0, (reader["REORDER_QTY"].ToString() != "") ? decimal.Parse(reader["REORDER_QTY"].ToString()) : 0M, (reader["STD_ORDER_COST"].ToString() != "") ? float.Parse(reader["STD_ORDER_COST"].ToString()) : 0f, (reader["CARRY_COST"].ToString() != "") ? float.Parse(reader["CARRY_COST"].ToString()) : 0f, reader["ABC"].ToString(), (reader["CYCLE_COUNT_FREQUENCY"].ToString() != "") ? int.Parse(reader["CYCLE_COUNT_FREQUENCY"].ToString()) : 0, (reader["LAST_CYCLE_COUNT"].ToString() != "") ? DateTime.Parse(reader["LAST_CYCLE_COUNT"].ToString()) : (CS00003 = new DateTime()), reader["CC_DISCREPAN_CYRULE"].ToString(), reader["SKU_GROUP"].ToString(), reader["SKU_GROUP2"].ToString(), reader["TRANSPORT_MODE"].ToString(), reader["FREIGHT_CLASS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["B_UDF1"].ToString(), reader["B_UDF2"].ToString(), reader["B_UDF3"].ToString(), reader["B_UDF4"].ToString(), reader["B_UDF5"].ToString(), reader["B_UDF6"].ToString(), reader["B_UDF7"].ToString(), reader["B_UDF8"].ToString(), reader["B_UDF9"].ToString(), reader["B_UDF10"].ToString(), reader["REMARK1"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["ITEM_NO_ID"].ToString());
                }
            }
            return skuInfo;
        }

        public SkuInfo GetSkuInfoByUserOwner(DataBase dataBase, DbTransaction tran, string skuID, string userID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = " SELECT  SKU.ROWID AS ROWID, SKU.WH_ID AS WH_ID, SKU.OWNER_ID AS OWNER_ID,SKU.ITEM_NO_ID, SKU.SKU_ID AS SKU_ID, SKU.DESCR AS DESCR, SKU.DESCR2 AS DESCR2, SKU.PACK_ID AS PACK_ID, SKU.CARTON_GROUP AS CARTON_GROUP, SKU.LOT_TEMPLATE_ID AS LOT_TEMPLATE_ID, SKU.TARIFF_ID AS TARIFF_ID, SKU.SKU_CLASS_ID AS SKU_CLASS_ID, SKU.HAZMAT_CODE AS HAZMAT_CODE, SKU.CUSTOM_SKU_NO AS CUSTOM_SKU_NO, SKU.COUNTRY_OF_ORIGIN AS COUNTRY_OF_ORIGIN, SKU.SHELF_LIFE_INDICATOR AS SHELF_LIFE_INDICATOR, SKU.SHELF_LIFE_CODE_TYPE AS SHELF_LIFE_CODE_TYPE, SKU.SHELF_LIFE_ON_RECEIVING AS SHELF_LIFE_ON_RECEIVING, SKU.SHELF_LIFE AS SHELF_LIFE, SKU.DAYS_TO_EXPIRE AS DAYS_TO_EXPIRE, SKU.LENGTH AS LENGTH, SKU.WIDTH AS WIDTH, SKU.HEIGHT AS HEIGHT, SKU.CUBE AS CUBE, SKU.GROSS_WGT AS GROSS_WGT, SKU.NET_WGT AS NET_WGT, SKU.TARE_WGT AS TARE_WGT, SKU.SKU_SIZE AS SKU_SIZE, SKU.SKU_COLOR AS SKU_COLOR, SKU.SKU_STYLE AS SKU_STYLE, SKU.COST_PRICE AS COST_PRICE, SKU.SALE_PRICE AS SALE_PRICE, SKU.NET_PRICE AS NET_PRICE, SKU.IS_SALE_PROMPTION AS IS_SALE_PROMPTION, SKU.RECEIPT_VALIDATION_TEMPLATE AS RECEIPT_VALIDATION_TEMPLATE, SKU.DEFAULT_IN_UOM AS DEFAULT_IN_UOM, SKU.DEFAULT_OUT_UOM AS DEFAULT_OUT_UOM, SKU.RF_DEFAULT_PACK AS RF_DEFAULT_PACK, SKU.PUTAWAY_RULE_ID AS PUTAWAY_RULE_ID, SKU.PREALLOCATE_RULE_ID AS PREALLOCATE_RULE_ID, SKU.ALLOCATE_RULE_ID AS ALLOCATE_RULE_ID, SKU.ROTATION_RULE_ID AS ROTATION_RULE_ID, SKU.CALCULATE_PUTAWAY_LOCATION AS CALCULATE_PUTAWAY_LOCATION, SKU.RECEIPT_VALIDATION_ID AS RECEIPT_VALIDATION_ID, SKU.ZONE_ID AS ZONE_ID, SKU.PUTAWAY_LOC AS PUTAWAY_LOC, SKU.RECEIPT_INSPECTION_LOC AS RECEIPT_INSPECTION_LOC, SKU.QC_LOC_OUT AS QC_LOC_OUT, SKU.RETURNS_LOC AS RETURNS_LOC, SKU.QC_LOC AS QC_LOC, SKU.MAX_QTY AS MAX_QTY, SKU.MIN_QTY AS MIN_QTY, SKU.REORDER_POINT AS REORDER_POINT, SKU.REORDER_QTY AS REORDER_QTY, SKU.STD_ORDER_COST AS STD_ORDER_COST, SKU.CARRY_COST AS CARRY_COST, SKU.ABC AS ABC, SKU.CYCLE_COUNT_FREQUENCY AS CYCLE_COUNT_FREQUENCY, SKU.LAST_CYCLE_COUNT AS LAST_CYCLE_COUNT, SKU.CC_DISCREPAN_CYRULE AS CC_DISCREPAN_CYRULE, SKU.SKU_GROUP AS SKU_GROUP, SKU.SKU_GROUP2 AS SKU_GROUP2, SKU.TRANSPORT_MODE AS TRANSPORT_MODE, SKU.FREIGHT_CLASS AS FREIGHT_CLASS, SKU.IS_ASSEMBLED AS IS_ASSEMBLED, SKU.UDF1 AS UDF1, SKU.UDF2 AS UDF2, SKU.UDF3 AS UDF3, SKU.UDF4 AS UDF4, SKU.UDF5 AS UDF5, SKU.UDF6 AS UDF6, SKU.UDF7 AS UDF7, SKU.UDF8 AS UDF8, SKU.UDF9 AS UDF9, SKU.UDF10 AS UDF10, SKU.B_UDF1 AS B_UDF1, SKU.B_UDF2 AS B_UDF2, SKU.B_UDF3 AS B_UDF3, SKU.B_UDF4 AS B_UDF4, SKU.B_UDF5 AS B_UDF5, SKU.B_UDF6 AS B_UDF6, SKU.B_UDF7 AS B_UDF7, SKU.B_UDF8 AS B_UDF8, SKU.B_UDF9 AS B_UDF9, SKU.B_UDF10 AS B_UDF10, SKU.REMARK1 AS REMARK1, SKU.REMARK2 AS REMARK2, SKU.IS_ACTIVE AS IS_ACTIVE, SKU.TS AS TS, SKU.CREATED_BY AS CREATED_BY, SKU.CREATED_DATE AS CREATED_DATE, SKU.UPDATED_BY AS UPDATED_BY, SKU.UPDATED_DATE AS UPDATED_DATE FROM @_@SKU SKU ".Replace("@_@", whLoginID) + " WHERE SKU.SKU_ID = @SKU_ID AND SKU.OWNER_ID IN (SELECT OWNER_ID FROM SYSADM.USER_OWNER WHERE USER_ID = @USER_ID) ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID), new SqlParameter("@SKU_ID", skuID) };
            SkuInfo skuInfo = null;
            using (IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    skuInfo = new SkuInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["DESCR2"].ToString(), reader["PACK_ID"].ToString(), reader["CARTON_GROUP"].ToString(), reader["LOT_TEMPLATE_ID"].ToString(), reader["TARIFF_ID"].ToString(), reader["SKU_CLASS_ID"].ToString(), reader["HAZMAT_CODE"].ToString(), reader["CUSTOM_SKU_NO"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["SHELF_LIFE_INDICATOR"].ToString(), reader["SHELF_LIFE_CODE_TYPE"].ToString(), (reader["SHELF_LIFE_ON_RECEIVING"].ToString() != "") ? int.Parse(reader["SHELF_LIFE_ON_RECEIVING"].ToString()) : 0, (reader["SHELF_LIFE"].ToString() != "") ? int.Parse(reader["SHELF_LIFE"].ToString()) : 0, (reader["DAYS_TO_EXPIRE"].ToString() != "") ? int.Parse(reader["DAYS_TO_EXPIRE"].ToString()) : 0, (reader["LENGTH"].ToString() != "") ? float.Parse(reader["LENGTH"].ToString()) : 0f, (reader["WIDTH"].ToString() != "") ? float.Parse(reader["WIDTH"].ToString()) : 0f, (reader["HEIGHT"].ToString() != "") ? float.Parse(reader["HEIGHT"].ToString()) : 0f, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["TARE_WGT"].ToString() != "") ? float.Parse(reader["TARE_WGT"].ToString()) : 0f, reader["SKU_SIZE"].ToString(), reader["SKU_COLOR"].ToString(), reader["SKU_STYLE"].ToString(), (reader["COST_PRICE"].ToString() != "") ? decimal.Parse(reader["COST_PRICE"].ToString()) : 0M, (reader["SALE_PRICE"].ToString() != "") ? decimal.Parse(reader["SALE_PRICE"].ToString()) : 0M, (reader["NET_PRICE"].ToString() != "") ? decimal.Parse(reader["NET_PRICE"].ToString()) : 0M, reader["IS_SALE_PROMPTION"].ToString(), reader["RECEIPT_VALIDATION_TEMPLATE"].ToString(), reader["DEFAULT_IN_UOM"].ToString(), reader["DEFAULT_OUT_UOM"].ToString(), reader["RF_DEFAULT_PACK"].ToString(), reader["PUTAWAY_RULE_ID"].ToString(), reader["PREALLOCATE_RULE_ID"].ToString(), reader["ALLOCATE_RULE_ID"].ToString(), reader["ROTATION_RULE_ID"].ToString(), reader["CALCULATE_PUTAWAY_LOCATION"].ToString(), reader["RECEIPT_VALIDATION_ID"].ToString(), reader["ZONE_ID"].ToString(), reader["PUTAWAY_LOC"].ToString(), reader["RECEIPT_INSPECTION_LOC"].ToString(), reader["QC_LOC_OUT"].ToString(), reader["RETURNS_LOC"].ToString(), reader["QC_LOC"].ToString(), (reader["MAX_QTY"].ToString() != "") ? decimal.Parse(reader["MAX_QTY"].ToString()) : 0M, (reader["MIN_QTY"].ToString() != "") ? decimal.Parse(reader["MIN_QTY"].ToString()) : 0M, (reader["REORDER_POINT"].ToString() != "") ? int.Parse(reader["REORDER_POINT"].ToString()) : 0, (reader["REORDER_QTY"].ToString() != "") ? decimal.Parse(reader["REORDER_QTY"].ToString()) : 0M, (reader["STD_ORDER_COST"].ToString() != "") ? float.Parse(reader["STD_ORDER_COST"].ToString()) : 0f, (reader["CARRY_COST"].ToString() != "") ? float.Parse(reader["CARRY_COST"].ToString()) : 0f, reader["ABC"].ToString(), (reader["CYCLE_COUNT_FREQUENCY"].ToString() != "") ? int.Parse(reader["CYCLE_COUNT_FREQUENCY"].ToString()) : 0, (reader["LAST_CYCLE_COUNT"].ToString() != "") ? DateTime.Parse(reader["LAST_CYCLE_COUNT"].ToString()) : (CS00003 = new DateTime()), reader["CC_DISCREPAN_CYRULE"].ToString(), reader["SKU_GROUP"].ToString(), reader["SKU_GROUP2"].ToString(), reader["TRANSPORT_MODE"].ToString(), reader["FREIGHT_CLASS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["B_UDF1"].ToString(), reader["B_UDF2"].ToString(), reader["B_UDF3"].ToString(), reader["B_UDF4"].ToString(), reader["B_UDF5"].ToString(), reader["B_UDF6"].ToString(), reader["B_UDF7"].ToString(), reader["B_UDF8"].ToString(), reader["B_UDF9"].ToString(), reader["B_UDF10"].ToString(), reader["REMARK1"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["ITEM_NO_ID"].ToString());
                }
            }
            return skuInfo;
        }

        public List<SysCode4DDL> GetSysCode4DDL(string language, string whLoginID)
        {
            string sql = "SELECT SC.SYS_CODE_TYPE, TL.[TEXT] AS SYS_CODE_DESCR FROM @_@SYS_CODE SC  LEFT JOIN SYSADM.TRANSLATION TL ON SC.SYS_CODE_TYPE = TL.ITEM_ID AND TL.ITEM_TYPE = 'SYS_CODE'   WHERE TL.LANG+'-'+TL.COUNTRY = @LANG ";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANG", language) };
            List<SysCode4DDL> sysCode4DDLS = new List<SysCode4DDL>();
            SysCode4DDL sysCode = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    sysCode = new SysCode4DDL(reader["SYS_CODE_TYPE"].ToString(), reader["SYS_CODE_DESCR"].ToString());
                    sysCode4DDLS.Add(sysCode);
                }
            }
            return sysCode4DDLS;
        }

        public List<SysCode4DDL> GetSysCodeForDropDownList(string sysCodeType, string language, string whLoginID)
        {
            string sql = "SELECT SCD.SYS_CODE AS SYS_CODE_VALUE, ISNULL(TL.[TEXT],SCD.DESCRIPTION) AS SYS_CODE_TEXT FROM @_@SYS_CODE_DETAIL SCD  LEFT JOIN SYSADM.TRANSLATION TL ON SCD.SYS_CODE_TYPE+'.'+SCD.SYS_CODE = TL.ITEM_ID   AND TL.LANG+'-'+TL.COUNTRY = @LANG AND TL.ITEM_TYPE = 'SYS_CODE_DETAIL'  WHERE SCD.SYS_CODE_TYPE = @SYS_CODE_TYPE AND SCD.IS_ACTIVE = 'Y'  ORDER BY SEQ_NO ";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@SYS_CODE_TYPE", sysCodeType), new SqlParameter("@LANG", language) };
            List<SysCode4DDL> sysCode4DDLS = new List<SysCode4DDL>();
            SysCode4DDL sysCode = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    sysCode = new SysCode4DDL(reader["SYS_CODE_VALUE"].ToString(), reader["SYS_CODE_TEXT"].ToString());
                    sysCode4DDLS.Add(sysCode);
                }
            }
            return sysCode4DDLS;
        }

        public List<UomList> GetUomListByPackID(string packID, string whLoginID)
        {
            string sql = " SELECT PACK_UOM1, PACK_UOM2, PACK_UOM3, PACK_UOM4, PACK_UOM5, PACK_UOM6, PACK_UOM7, PACK_UOM8, PACK_UOM9 FROM @_@PACK WHERE IS_ACTIVE = 'Y' AND PACK_ID = @PACK_ID;  SELECT QTY,INNER_PACK,CASE_CNT,PALLET,CUBE,NET_WGT,OTHER_UNIT1,OTHER_UNIT2 FROM @_@PACK WHERE IS_ACTIVE = 'Y' AND PACK_ID = @PACK_ID ";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PACK_ID", packID) };
            string sql2 = "SELECT UOM_ID, UOM_DESCR FROM @_@UOM WHERE IS_ACTIVE = 'Y'";
            sql2 = sql2.Replace("@_@", whLoginID);
            List<UomList> uoms = new List<UomList>();
            UomList uom = null;
            using (DataSet packUomDs = DBHelper.ExecuteDataSet(CommandType.Text, sql, paras))
            {
                if ((packUomDs == null) || (packUomDs.Tables[0].Rows.Count <= 0))
                {
                    return uoms;
                }
                DataSet uomDs = DBHelper.ExecuteDataSet(CommandType.Text, sql2);
                for (int i = 0; i < 9; i++)
                {
                    if (!string.IsNullOrEmpty(packUomDs.Tables[0].Rows[0][i].ToString().Trim()))
                    {
                        uom = new UomList();
                        uom.UomID = packUomDs.Tables[0].Rows[0][i].ToString().Trim();
                        uom.UomQty = float.Parse(string.IsNullOrEmpty(packUomDs.Tables[1].Rows[0][i].ToString().Trim()) ? "0" : packUomDs.Tables[1].Rows[0][i].ToString().Trim());
                        DataRow[] drs = uomDs.Tables[0].Select("UOM_ID='" + packUomDs.Tables[0].Rows[0][i].ToString().Trim() + "'");
                        uom.UomDescr = drs[0][1].ToString();
                        uoms.Add(uom);
                    }
                }
            }
            return uoms;
        }

        public List<Owner4DDL> GetUserPermissionOwner(string userID)
        {
            string SQL_SELECT_OWNER = "SELECT UO.OWNER_ID,OWNER.COMPANY FROM SYSADM.USER_OWNER UO LEFT JOIN @_@OWNER OWNER ON UO.OWNER_ID = OWNER.OWNER_ID WHERE USER_ID = @USER_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID) };
            List<Owner4DDL> ownerList = new List<Owner4DDL>();
            Owner4DDL owner = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, SQL_SELECT_OWNER, paras))
            {
                while (reader.Read())
                {
                    owner = new Owner4DDL(reader["OWNER_ID"].ToString(), reader["COMPANY"].ToString());
                    ownerList.Add(owner);
                }
            }
            return ownerList;
        }

        public List<WareHouseName4DDL> GetUserPermissionWareHouse(string userID)
        {
            string SQL_SELECT_WAREHOUSE = "SELECT UW.WH_DB_ID,DC.WH_DB_DESC,DC.DB_LOGIN_ID FROM SYSADM.USER_WH UW  LEFT JOIN SYSADM.DB_CONFIG DC ON UW.WH_DB_ID = DC.WH_DB_ID WHERE WH_DB_NAME <> 'GLOBAL' AND USER_ID = @USER_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", userID) };
            List<WareHouseName4DDL> WareHouseNameList = new List<WareHouseName4DDL>();
            WareHouseName4DDL WareHouseName = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, SQL_SELECT_WAREHOUSE, paras))
            {
                while (reader.Read())
                {
                    WareHouseName = new WareHouseName4DDL(int.Parse(reader["WH_DB_ID"].ToString()), reader["WH_DB_DESC"].ToString(), reader["DB_LOGIN_ID"].ToString());
                    WareHouseNameList.Add(WareHouseName);
                }
            }
            return WareHouseNameList;
        }

        public List<WareHouseName4DDL> GetWareHouse4Select()
        {
            string SQL_SELECT_WAREHOUSE = "SELECT WH_DB_ID,WH_DB_DESC FROM SYSADM.DB_CONFIG WHERE WH_DB_NAME <> 'GLOBAL'";
            List<WareHouseName4DDL> WareHouseNameList = new List<WareHouseName4DDL>();
            WareHouseName4DDL WareHouseName = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, SQL_SELECT_WAREHOUSE))
            {
                while (reader.Read())
                {
                    WareHouseName = new WareHouseName4DDL(int.Parse(reader["WH_DB_ID"].ToString()), reader["WH_DB_DESC"].ToString(), "");
                    WareHouseNameList.Add(WareHouseName);
                }
            }
            return WareHouseNameList;
        }

        public DataSet SearchDataByKey(string key, string key2, string Type, string whLoginID, PubParasInfo pubParasInfo)
        {
            string strSql = string.Empty;
            string CS40001 = Type;
            if (CS40001 != null)
            {
                if (!(CS40001 == "SKU"))
                {
                    if (CS40001 == "LOC")
                    {
                        strSql = "SELECT S.lOC,S.ZONE_ID FROM @_@LOCATION S  WHERE S.lOC LIKE '%" + key + "%'";
                    }
                }
                else
                {
                    strSql = "SELECT S.SKU_ID,S.DESCR FROM @_@SKU S INNER JOIN SYSADM.USER_OWNER UO ON S.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = '@USER_ID' WHERE S.OWNER_ID='" + key2 + "' AND  S.SKU_ID LIKE '%" + key + "%' AND S.IS_ACTIVE='Y' ";
                }
            }
            strSql = strSql.Replace("@_@", whLoginID).Replace("@USER_ID", pubParasInfo.UserID);
            return DBHelper.ExecuteDataSet(CommandType.Text, strSql);
        }

        public string SqlAnalyse(string strSql, string whLoginID)
        {
            strSql = strSql.Replace("@_@", whLoginID);
            try
            {
                DBHelper.ExecuteNonQuery(CommandType.Text, strSql);
                return "";
            }
            catch (SqlException ex)
            {
                return ex.Message;
            }
        }

        public DataSet SqlQuery(string sql, BaseQuery baseQuery)
        {
            string strSQL = sql;
            if (!baseQuery.IsGetAll)
            {
                strSQL = PagingHelper.GetPagingSQL(sql, baseQuery.CurrentPage, baseQuery.PageSize, baseQuery.SortField, baseQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, strSQL);
        }

        public int UpdateASNAndSOSingleField(string orderType, string orderID, string updatedField, string value, string updatedBy, string whLoginID)
        {
            int result = 0;
            string sql = string.Empty;
            try
            {
                if (orderType.ToUpper() == "ASN")
                {
                    sql = "UPDATE @_@RECEIPT SET " + updatedField + " = @UPDATED_VALUE, UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @ORDER_ID ";
                }
                else if (orderType.ToUpper() == "SO")
                {
                    sql = "UPDATE @_@ORDERS SET " + updatedField + " = @UPDATED_VALUE, UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID ";
                }
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UPDATED_VALUE", value), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID) };
                sql = sql.Replace("@_@", whLoginID);
                result = DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateDeclarations(string Type, string keyID, string ExternOrderID, string CoreReleaseID, string WorkOrder, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            string StrSql = "";
            string CS40001 = Type;
            if (CS40001 != null)
            {
                if (!(CS40001 == "RECEIPT"))
                {
                    if (CS40001 == "SHIPPING")
                    {
                        StrSql = "UPDATE @_@ORDERS SET EXTERN_ORDER_ID=@EXTERNAL_ORDER_ID,CORE_RELEASE_ID=@CORE_RELEASE_ID,WORK_ORDER=@WORK_ORDER WHERE ORDER_ID=@KEYID";
                    }
                }
                else
                {
                    StrSql = "UPDATE @_@RECEIPT SET EXTERNAL_RECEIPT_ID=@EXTERNAL_ORDER_ID,CORE_RELEASE_ID=@CORE_RELEASE_ID,WORK_ORDER=@WORK_ORDER  WHERE RECEIPT_ID=@KEYID";
                }
            }
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@KEYID", keyID), new SqlParameter("@CORE_RELEASE_ID", CoreReleaseID), new SqlParameter("@WORK_ORDER", WorkOrder), new SqlParameter("@EXTERNAL_ORDER_ID", ExternOrderID) };
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, StrSql.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateExternOrderID(string Type, string keyID, string ExternOrderID, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            string StrSql = "";
            string CS40001 = Type;
            if (CS40001 != null)
            {
                if (!(CS40001 == "RECEIPT"))
                {
                    if (CS40001 == "SHIPPING")
                    {
                        StrSql = "UPDATE @_@ORDERS SET EXTERN_ORDER_ID=@EXTERNAL_ORDER_ID WHERE ORDER_ID=@KEYID;UPDATE @_@ORDER_DETAIL SET EXTERN_ORDER_ID=@EXTERNAL_ORDER_ID WHERE ORDER_ID=@KEYID;UPDATE @_@SALES_ORDER SET EXTERN_ORDER_ID=@EXTERNAL_ORDER_ID WHERE ORDER_ID=(SELECT REFERENCE_NUM FROM @_@ORDERS WHERE ORDER_ID=@KEYID);";
                    }
                }
                else
                {
                    StrSql = "UPDATE @_@RECEIPT SET EXTERNAL_RECEIPT_ID=@EXTERNAL_ORDER_ID WHERE RECEIPT_ID=@KEYID;UPDATE @_@RECEIPT_DETAIL SET EXTERN_RECEIPT_ID=@EXTERNAL_ORDER_ID WHERE RECEIPT_ID=@KEYID;UPDATE @_@RECEIPT_DETAIL_LPN SET EXTERN_RECEIPT_ID=@EXTERNAL_ORDER_ID WHERE RECEIPT_ID=@KEYID;UPDATE @_@PURCHASE_ORDER SET EXTERNAL_PO_ID=@EXTERNAL_ORDER_ID WHERE PO_ID=(SELECT PO_ID FROM @_@RECEIPT WHERE RECEIPT_ID=@KEYID);";
                }
            }
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@KEYID", keyID), new SqlParameter("@EXTERNAL_ORDER_ID", ExternOrderID) };
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, StrSql.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateUDF(string movementID, UDFInfo udfInfo, string Type, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            string StrSql = "";
            string CS40001 = Type;
            if (CS40001 != null)
            {
                if (!(CS40001 == "MOVEMENT"))
                {
                    if (CS40001 == "RECEIPT")
                    {
                        StrSql = "UPDATE @_@RECEIPT SET UDF1 =@UDF1,UDF2 =@UDF2,UDF3 =@UDF3,UDF4 =@UDF4,UDF5 =@UDF5,UDF6 =@UDF6,UDF7 =@UDF7,UDF8 =@UDF8,UDF9 =@UDF9,UDF10 =@UDF10, REMARK =@REMARK,UPDATED_BY=@UPDATED_BY,UPDATED_DATE=GetDate() WHERE RECEIPT_ID= @ID";
                    }
                }
                else
                {
                    StrSql = "UPDATE @_@MOVEMENT SET UDF1 =@UDF1,UDF2 =@UDF2,UDF3 =@UDF3,UDF4 =@UDF4,UDF5 =@UDF5,UDF6 =@UDF6,UDF7 =@UDF7,UDF8 =@UDF8,UDF9 =@UDF9,UDF10 =@UDF10, REMARK =@REMARK,UPDATED_BY=@UPDATED_BY,UPDATED_DATE=GetDate() WHERE MOVEMENT_ID= @ID";
                }
            }
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UDF1", udfInfo.Udf1), new SqlParameter("@UDF2", udfInfo.Udf2), new SqlParameter("@UDF3", udfInfo.Udf3), new SqlParameter("@UDF4", udfInfo.Udf4), new SqlParameter("@UDF5", udfInfo.Udf5), new SqlParameter("@UDF6", udfInfo.Udf6), new SqlParameter("@UDF7", udfInfo.Udf7), new SqlParameter("@UDF8", udfInfo.Udf8), new SqlParameter("@UDF9", udfInfo.Udf9), new SqlParameter("@UDF10", udfInfo.Udf10), new SqlParameter("@REMARK", udfInfo.Remark), new SqlParameter("@ID", movementID), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, StrSql.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
    }
}

